using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using DBMS.Models.Query;
using DBMS.Models;
using DBMS.Models.DataInterfaces;
using DBMS.Data;

namespace DBMS.Controllers
{
    public class QueryController : Controller
    {
        private ITableRepository tableRepository;
        private IDatabaseRepository databaseRepository;

        private enum ComparisonOperators { LESS_THAN, GREATER_THAN, EQUALS, NOT_EQUALS };
        private enum AggregationFunctions { SUM, AVG, MIN, MAX, COUNT };

        public QueryController()
        {
            this.tableRepository = new TableRepository();
            this.databaseRepository = new DatabaseRepository();
        }

        public ActionResult Create(string databaseId) 
        {
            Database database = databaseRepository.GetDatabase(Int32.Parse(databaseId));

            ViewBag.Database = database;

            return View();
        }

        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Create(string queryFields, IList<Field> query) 
        {
            if (!ValidateQuery(query)) {
                ViewData["queryFields"] = queryFields;
                return View();
            }

            StringBuilder queryString = new StringBuilder();

            foreach (Field field in query) 
            {
                queryString.AppendFormat("{0} {1} {2} {3}", field.AttachWith, field.Name, field.Operator, field.Value);
                queryString.AppendLine();
            }

            ViewData["queryString"] = queryString.ToString();

            return View("Results");
        }

        public ActionResult Fields()
        {
            ICollection<FieldDefinition> values = FieldDefinitions.Fields.Values;
           return Json(values, JsonRequestBehavior.AllowGet);
        }

        public ActionResult Tables(string databaseId)
        {
            IList<Table> tables = tableRepository.GetAllTablesForDatabase(Int32.Parse(databaseId));

            IDictionary<string, FieldDefinition> fields = new Dictionary<string, FieldDefinition>();
            foreach (Table table in tables)
            {
                fields[table.Name] = new FieldDefinition { Name = table.Name, Operators = null, Type = Types.Text };
            }

            ICollection<FieldDefinition> values = fields.Values;
            return Json(values, JsonRequestBehavior.AllowGet);
        }

        private bool ValidateQuery(IList<Field> query) 
        {
            if (query == null) 
            {
                ModelState.AddModelError("__FORM", "At least one query field is required.");
            }
            else 
            {
                int i = 0;
                foreach (Field field in query) {
                    if (String.IsNullOrEmpty(field.Value)) {
                        ModelState.AddModelError("query[" + i + "].Value", "You must specify a value for this field.");
                    }
                    i++;
                }
            }

            return ModelState.IsValid;
        }

        public ActionResult Menu()
        {
            return PartialView();
        }

        public ActionResult ExecuteQuery(int databaseId, string query)
        {
            //localhost:10453/Query/ExecuteQuery?databaseId=4&query=SELECT col1,col2,col3 FROM 5 WHERE colX > 2 AND WHERE colX2 = 'stringValue'
            //localhost:10453/Query/ExecuteQuery?databaseId=4&query=SELECT FirstName,LastName,Age FROM 5 WHERE Age > 20
            //localhost:10453/Query/ExecuteQuery?databaseId=4&query=SELECT FirstName,LastName,Age FROM 2 WHERE Age > 20
            string[] strSeparators = new string[] { "SELECT", "FROM", "WHERE" };
            string[] queryParts = query.Split(strSeparators, StringSplitOptions.None);
            string p1 = queryParts[0];
            // query ex:
            //   0          1             2        3                   4
            // SELECT col1,col2,col3 FROM 1 WHERE colX > 2 AND WHERE colX2 = 'stringValue'
            // NOTE: no space after commas between column names

            string[] selectedColNames = queryParts[1].Split(',');
            int tableId = getTableIdByName(databaseId, queryParts[2]);//Int32.Parse(queryParts[2]);
            List<string> aggregationFuncs = new List<string>();
            List<string> colNamesInWhereClauses = new List<string>();
            List<string> whereClauseElems = new List<string>();
            for (int ind = 3; ind < queryParts.Length; ++ind)
            {
                string whereClauseElem = queryParts[ind].Trim();
                whereClauseElems.Add(whereClauseElem);

                string colName = whereClauseElem.Split(' ')[0];
                colNamesInWhereClauses.Add(colName);
            }

            ColumnRepository colRep = new ColumnRepository();
            IList<Column> allColumns = colRep.GetAllColumnsForTable(databaseId, tableId);
            List<Column> selectedColumns = new List<Column>();
            List<Column> whereClauseColumns = new List<Column>();
            List<Column> aggregFuncCol = new List<Column>();

            foreach (Column column in allColumns)
            {
                foreach (string columnName in selectedColNames)
                {
                    if (columnName.Trim().Equals(column.Name))
                    {
                        selectedColumns.Add(column);
                    }
                    else
                    {
                        if (columnName.Trim().Contains(' '))
                        {
                            string[] splitAt = { " " };
                            string[] aggregColParts = columnName.Split(splitAt, StringSplitOptions.RemoveEmptyEntries);
                            string aggregFuncName = aggregColParts[0].Trim();
                            string aggregColName = aggregColParts[1].Trim();
                            if (isNameOfAggregationFunc(aggregFuncName) && aggregColName.Equals(column.Name))
                            {
                                aggregationFuncs.Add(aggregFuncName);
                                aggregFuncCol.Add(column);
                            }
                        }
                    }
                }

                foreach (string columnName in colNamesInWhereClauses)
                {
                    if (columnName.Equals(column.Name))
                    {
                        whereClauseColumns.Add(column);
                    }
                }
            }

            int rowCount = 0;
            List<int> rowIds = new List<int>();
            int colCount = selectedColumns.Count;
            if (selectedColumns.Count > 0)
            {
                rowCount = selectedColumns[0].Values.Count;
                foreach (Value val in selectedColumns[0].Values)
                {
                    rowIds.Add(val.RowId);
                }
            }
            if (aggregFuncCol.Count == 1)
            {
                colCount += 1;
                if (rowCount == 0)
                {
                    rowCount = aggregFuncCol[0].Values.Count;
                    foreach (Value val in aggregFuncCol[0].Values)
                    {
                        rowIds.Add(val.RowId);
                    }
                }
            }

            List<int> selectedRows = new List<int>();
            foreach (int currRow in rowIds)
            {
                //int selRow = rowMatchesTheCriterias(whereClauseElems, whereClauseColumns, currRow);
                if (rowMatchesTheCriterias(whereClauseElems, whereClauseColumns, currRow))
                {
                    selectedRows.Add(currRow);
                }
            }

            ValueRepository valueRepository = new ValueRepository();

            IList<Value> values = new List<Value>();
            if ((colCount > 1) || ((colCount == 1) && (aggregFuncCol.Count == 0)))
            {
                values = valueRepository.GetValuesForTable(databaseId, tableId).Where(x => selectedRows.Contains(x.RowId)).ToList();
            }

            if (aggregFuncCol.Count == 1)
            {
                IList<Value> aggregValues = getValuesOfColumnForRowIds(allColumns, aggregFuncCol[0].Name, selectedRows);
                AggregationFunctions aggregationFunction = stringToAggregationFunction(aggregationFuncs[0]);
                string aggregFuncResult = EvaluateAggregationFunction(aggregValues, aggregationFunction, aggregFuncCol[0].Type);

                Column aggregResCol = new Column();
                aggregResCol.Index = 100;
                aggregResCol.Name = aggregationFuncs[0] + "_" + aggregFuncCol[0].Name;
                aggregResCol.Values = new List<Value>();

                if (colCount == 1)
                {
                    Value value = new Value();
                    value.ColumnId = aggregResCol.Index;
                    value.ConcreteValue = aggregFuncResult;
                    value.RowId = 1;
                    aggregResCol.Values.Add(value);
                    values.Add(value);
                    selectedRows.Clear();
                    selectedRows.Add(1);
                }
                else if (colCount > 1)
                {
                    int cnt = 1;
                    foreach (int selRow in selectedRows)
                    {
                        Value value = new Value();
                        value.ColumnId = aggregResCol.Index;
                        value.ConcreteValue = aggregFuncResult;
                        value.RowId = selRow;
                        aggregResCol.Values.Add(value);
                        //values.Insert(cnt * colCount, value);
                        values.Add(value);
                        ++cnt;
                    }

                    /*for (int cnt = 0; cnt < selectedRows.Count; ++cnt)
                    {
                        Value value = new Value();
                        value.ColumnId = aggregResCol.Index;
                        value.ConcreteValue = aggregFuncResult;
                        aggregResCol.Values.Add(value);
                        values.Insert((cnt + 1) * colCount, value);
                    }*/
                }
                selectedColumns.Add(aggregResCol);
            }

            ViewBag.Values = values;
            ViewBag.Rows = selectedRows;
            ViewBag.Columns = selectedColumns;

            return View();
        }

        private int getTableIdByName(int databaseId, string tableName)
        {
            int result = -1;

            TableRepository tableRepository = new TableRepository();
            IList<Table> tables = tableRepository.GetAllTablesForDatabase(databaseId);

            foreach (Table table in tables)
            {
                if (table.Name.Equals(tableName.Trim()))
                {
                    result = table.Index;
                    break;
                }
            }

            return result;
        }

        private IList<Value> getValuesOfColumnForRowIds(IList<Column> columns, string columnName, IList<int> rowIds)
        {
            IList<Value> retValues = new List<Value>();

            foreach (int rowId in rowIds)
            {
                Value resValue = getValueByColumnNameAndRowId(columns, columnName, rowId);
                retValues.Add(resValue);
            }

            return retValues;
        }

        private bool rowMatchesTheCriterias(List<string> whereClauseElems, List<Column> whereClauseColumns, int rowId)
        {
            bool matches = false;

            string whereClausesEvalPartialRes = "";
            foreach (string whereClauseElem in whereClauseElems)
            {
                whereClausesEvalPartialRes += EvaluateWhereClausePartially(whereClauseElem, whereClauseColumns, rowId);
            }

            matches = EvaluateWhereClausesFinalResult(whereClausesEvalPartialRes);

            return matches;
        }

        private string EvaluateWhereClausePartially(string whereClause, List<Column> whereClauseColumns, int rowId)
        {
            string whereClausePartialResult = "";

            // 0 1 2  3  4 5 6 7  8 9 10
            // a > 1
            // a > 1 AND
            // a > 1 AND b < 5 OR c = 9
            int initPosOfLeftSide = 0;
            int initPosOfCompOp = 1;
            int initPosOfRightSide = 2;
            int initPosOfLogicalOp = 3;

            int ind = 0;
            string[] whereClauseSubElems = whereClause.Split(' ');
            string columnName = whereClauseSubElems[0];
            string strComparisonOp = whereClauseSubElems[1];
            string strRightSide = whereClauseSubElems[2];

            bool comparisonResult = false;
            Value value = getValueByColumnNameAndRowId(whereClauseColumns, columnName, rowId);
            ComparisonOperators comparisonOperator = stringToComparisonOperator(strComparisonOp);
            if (strRightSide.StartsWith("'"))
            {
                string rightSideValue = strRightSide.Split('\'')[1];
                comparisonResult = EvaluateComparison(value, comparisonOperator, rightSideValue);
            }
            else
            {
                int rightSideValue = Int32.Parse(strRightSide);
                comparisonResult = EvaluateComparison(value, comparisonOperator, rightSideValue);
            }

            whereClausePartialResult = comparisonResult.ToString();
            if (whereClauseSubElems.Length == 4)
            {
                whereClausePartialResult += " " + whereClauseSubElems[3];
            }
            whereClausePartialResult += " ";

            return whereClausePartialResult;
        }

        private bool EvaluateWhereClausesFinalResult(string whereClauseEvalPartialRes)
        {
            bool retFinalResult = false;

            string[] strSeparators = new string[] { " " };
            string[] splitted = whereClauseEvalPartialRes.Split(strSeparators, StringSplitOptions.RemoveEmptyEntries);
            string logicalOpRes = bool.TrueString;

            if (splitted != null && splitted.Length == 1)
            {
                logicalOpRes = splitted[0];
            }
            else if (splitted != null)
            {
                while (splitted.Length >= 3)
                {
                    logicalOpRes = EvaluateLogicalOperation(splitted[0], splitted[1], splitted[2]);
                    whereClauseEvalPartialRes = rebuildPartialResString(splitted, logicalOpRes);
                    splitted = whereClauseEvalPartialRes.Split(' ');
                }
            }

            retFinalResult = (logicalOpRes.Equals(bool.TrueString)) ? true : false;

            return retFinalResult;
        }

        private string EvaluateLogicalOperation(string leftSide, string logicalOperator, string rightSide)
        {
            string result = "";

            bool bLeftSide = bool.Parse(leftSide);
            bool bRightSide = bool.Parse(rightSide);

            if (logicalOperator.Equals("AND"))
            {
                bool bResult = bLeftSide && bRightSide;
                result = bResult.ToString();
            }
            else if (logicalOperator.Equals("OR"))
            {
                bool bResult = bLeftSide || bRightSide;
                result = bResult.ToString();
            }

            return result;
        }

        private string rebuildPartialResString(string[] splitted, string logicalOpRes)
        {
            string rebuildRes = logicalOpRes;

            if (splitted.Length > 3)
            {
                for (int ind = 3; ind < splitted.Length; ++ind)
                {
                    rebuildRes += " " + splitted[ind];
                }
            }

            return rebuildRes.Trim();
        }

        private Value getValueByColumnNameAndRowId(IList<Column> columns, string columnName, int rowId)
        {
            Value retValue = null;

            foreach (Column column in columns)
            {
                if (column.Name.Equals(columnName))
                {
                    foreach (Value val in column.Values)
                    {
                        if (val.RowId.Equals(rowId))
                        {
                            retValue = val;
                            break;
                        }
                    }

                    if (!retValue.Equals(null))
                    {
                        break;
                    }

                }

            }

            return retValue;
        }

        private List<Value> GetValuesForRow(IList<Column> columns, int rowId)
        {
            List<Value> retValues = new List<Value>();

            foreach (Column column in columns)
            {
                retValues.Add(column.Values[rowId]);
            }

            return retValues;
        }

        private bool EvaluateComparison(Value valueToCompare, ComparisonOperators comparisonOperator, int rightSideValue)
        {
            bool result = false;

            int leftSideValue = Int32.Parse(valueToCompare.ConcreteValue);

            switch (comparisonOperator)
            {
                case ComparisonOperators.LESS_THAN:
                    result = leftSideValue < rightSideValue;
                    break;
                case ComparisonOperators.GREATER_THAN:
                    result = leftSideValue > rightSideValue;
                    break;
                case ComparisonOperators.EQUALS:
                    result = leftSideValue == rightSideValue;
                    break;
                case ComparisonOperators.NOT_EQUALS:
                    result = leftSideValue != rightSideValue;
                    break;
            }

            return result;
        }

        private bool EvaluateComparison(Value valueToCompare, ComparisonOperators comparisonOperator, string rightSideValue)
        {
            bool result = false;

            string leftSideValue = valueToCompare.ConcreteValue;

            switch (comparisonOperator)
            {
                case ComparisonOperators.LESS_THAN:
                    result = leftSideValue.CompareTo(rightSideValue) == -1;
                    break;
                case ComparisonOperators.GREATER_THAN:
                    result = leftSideValue.CompareTo(rightSideValue) == 1;
                    break;
                case ComparisonOperators.EQUALS:
                    result = leftSideValue.CompareTo(rightSideValue) == 0;
                    break;
                case ComparisonOperators.NOT_EQUALS:
                    result = leftSideValue.CompareTo(rightSideValue) != 0;
                    break;
            }

            return result;
        }

        private ComparisonOperators stringToComparisonOperator(string strComparisonOperator)
        {
            ComparisonOperators retComparisonOperator = ComparisonOperators.EQUALS;

            bool isLessThan = strComparisonOperator.CompareTo("<") == 0;
            bool isGreaterThan = strComparisonOperator.CompareTo(">") == 0;
            bool isEquals = strComparisonOperator.CompareTo("=") == 0;
            bool isNotEquals = strComparisonOperator.CompareTo("!=") == 0;

            if (isLessThan)
            {
                retComparisonOperator = ComparisonOperators.LESS_THAN;
            }
            else if (isGreaterThan)
            {
                retComparisonOperator = ComparisonOperators.GREATER_THAN;
            }
            else if (isEquals)
            {
                retComparisonOperator = ComparisonOperators.EQUALS;
            }
            else if (isNotEquals)
            {
                retComparisonOperator = ComparisonOperators.NOT_EQUALS;
            }

            return retComparisonOperator;
        }

        private string EvaluateAggregationFunction(IList<Value> values, AggregationFunctions aggregationFunction, string valueType)
        {
            string result = "";

            switch (aggregationFunction)
            {
                case AggregationFunctions.SUM:
                    result = ExecSUM(values, valueType);
                    break;
                case AggregationFunctions.AVG:
                    result = ExecAVG(values, valueType);
                    break;
                case AggregationFunctions.MIN:
                    result = ExecMIN(values, valueType);
                    break;
                case AggregationFunctions.MAX:
                    result = ExecMAX(values, valueType);
                    break;
                case AggregationFunctions.COUNT:
                    result = ExecCOUNT(values, valueType);
                    break;
            }

            return result;
        }

        private string ExecSUM(IList<Value> values, string valueType)
        {
            string result = "";

            if (valueType.Contains('.'))
            {
                double sum = 0.0;
                foreach (Value value in values)
                {
                    sum += Double.Parse(value.ConcreteValue);
                }
                result = sum.ToString();
            }
            else
            {
                int sum = 0;
                foreach (Value value in values)
                {
                    sum += Int32.Parse(value.ConcreteValue);
                }
                result = sum.ToString();
            }

            return result;
        }

        private string ExecAVG(IList<Value> values, string valueType)
        {
            string result = "";

            if (valueType.Contains('.'))
            {
                double sum = 0.0;
                foreach (Value value in values)
                {
                    sum += Double.Parse(value.ConcreteValue);
                }

                double avg = sum / values.Count;

                result = avg.ToString();
            }
            else
            {
                int sum = 0;
                foreach (Value value in values)
                {
                    sum += Int32.Parse(value.ConcreteValue);
                }

                int avg = sum / values.Count;

                result = avg.ToString();
            }

            return result;
        }

        private string ExecMIN(IList<Value> values, string valueType)
        {
            string result = "";

            if (valueType.Contains('.'))
            {
                double min = Double.Parse(values[0].ConcreteValue);
                foreach (Value value in values)
                {
                    double concrValue = Double.Parse(value.ConcreteValue);

                    if (concrValue < min)
                    {
                        min = concrValue;
                    }
                }
                result = min.ToString();
            }
            else
            {
                int min = Int32.Parse(values[0].ConcreteValue);
                foreach (Value value in values)
                {
                    int concrValue = Int32.Parse(value.ConcreteValue);

                    if (concrValue < min)
                    {
                        min = concrValue;
                    }
                }
                result = min.ToString();
            }

            return result;
        }

        private string ExecMAX(IList<Value> values, string valueType)
        {
            string result = "";

            if (valueType.Contains('.'))
            {
                double max = Double.Parse(values[0].ConcreteValue);
                foreach (Value value in values)
                {
                    double concrValue = Double.Parse(value.ConcreteValue);

                    if (concrValue > max)
                    {
                        max = concrValue;
                    }
                }
                result = max.ToString();
            }
            else
            {
                int max = Int32.Parse(values[0].ConcreteValue);
                foreach (Value value in values)
                {
                    int concrValue = Int32.Parse(value.ConcreteValue);

                    if (concrValue > max)
                    {
                        max = concrValue;
                    }
                }
                result = max.ToString();
            }

            return result;
        }

        private string ExecCOUNT(IList<Value> values, string valueType)
        {
            string result = values.Count.ToString();

            return result;
        }

        private bool isNameOfAggregationFunc(string name)
        {
            bool result = false;

            if (name != null)
            {
                if (name.Equals("SUM") || name.Equals("AVG") || name.Equals("MIN") || name.Equals("MAX") || name.Equals("COUNT"))
                {
                    result = true;
                }
            }

            return result;
        }

        private AggregationFunctions stringToAggregationFunction(string name)
        {
            AggregationFunctions aggregationFunction = AggregationFunctions.SUM;

            if (name.Equals("SUM"))
            {
                aggregationFunction = AggregationFunctions.SUM;
            }
            else if (name.Equals("AVG"))
            {
                aggregationFunction = AggregationFunctions.AVG;
            }
            else if (name.Equals("MIN"))
            {
                aggregationFunction = AggregationFunctions.MIN;
            }
            else if (name.Equals("MAX"))
            {
                aggregationFunction = AggregationFunctions.MAX;
            }
            else if (name.Equals("COUNT"))
            {
                aggregationFunction = AggregationFunctions.COUNT;
            }

            return aggregationFunction;
        }
    }
}
